﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using GroupManager.Class;
using GroupManager.Resource.CSDL;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace GroupManager.Class
{
    public class LopDanhSach
    {
        // Import từ Excel lên GridView
        public static DataTable ImportDanhSachTuFileExcel(FileUpload fileUpload)
        {
            fileUpload.SaveAs(HttpContext.Current.Server.MapPath(@"" + fileUpload.FileName));
            string path = HttpContext.Current.Server.MapPath(@"" + fileUpload.FileName);
            string connString = "";
            string strFileType = Path.GetExtension(path);

            //Connection String to Excel Workbook
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            string query = "SELECT [STT],[MaSV],[HoTen] FROM [Sheet1$]";
            OleDbConnection conn = new OleDbConnection(connString);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            da.Dispose();
            conn.Close();
            conn.Dispose();
            File.Delete(path);
            return ds.Tables[0];
        }

        // Lưu vào cơ sở từ GridView
        public static void LuuVaoCSDL(GridView GridView)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            for (int i = 0; i < GridView.Rows.Count; i++)
            {
                string masv = GridView.Rows[i].Cells[1].Text;
                string hoten = GridView.Rows[i].Cells[1].Text;
                ThanhVien thanhvien = new ThanhVien();
                thanhvien.MaSV = masv;
                thanhvien.HoTen = hoten;
                db.ThanhViens.InsertOnSubmit(thanhvien);
                db.SubmitChanges();
            }
        }

        // Export từ DataTable ra file Excel
        public static void ExportExcel(DataTable dt)
        {
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=DanhSach.xls");
            HttpContext.Current.Response.ContentType = "application/vnd.xls";
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite =
            new HtmlTextWriter(stringWrite);
            htmlWrite.AddStyleAttribute("font", "Times New Roman");
            GridView gridView = new GridView();
            gridView.DataSource = dt;
            gridView.DataBind();
            gridView.RenderControl(htmlWrite);
            HttpContext.Current.Response.Write(stringWrite.ToString());
            HttpContext.Current.Response.End();
        }
    }
}